import mysql.connector
import html
from typing import List, Dict


def connect_to_db() -> mysql.connector.connection.MySQLConnection:
    return mysql.connector.connect(
        host="localhost",
        user="root",
        password="lizecan0418",
        database="journeo"
    )


def get_text_columns() -> List[str]:
    """返回需要处理的文本类型列名"""
    return ['Name', 'Description', 'Address']


def decode_html_entities(text: str) -> str:
    """解码HTML实体"""
    if not text or not isinstance(text, str):
        return text
    return html.unescape(text)


def update_attractions():
    conn = connect_to_db()
    cursor = conn.cursor(dictionary=True)
    text_columns = get_text_columns()

    try:
        # 获取所有记录
        cursor.execute("SELECT AttractionId, " + ", ".join(text_columns) + " FROM attractions")
        rows = cursor.fetchall()

        # 处理每条记录
        for row in rows:
            updates = {}
            need_update = False

            # 检查每个文本列
            for column in text_columns:
                if row[column]:
                    decoded_text = decode_html_entities(row[column])
                    if decoded_text != row[column]:
                        updates[column] = decoded_text
                        need_update = True

            # 如果有需要更新的内容
            if need_update:
                update_query = "UPDATE attractions SET " + \
                               ", ".join([f"{col} = %s" for col in updates.keys()]) + \
                               " WHERE AttractionId = %s"

                cursor.execute(update_query, list(updates.values()) + [row['AttractionId']])
                print(f"Updated AttractionId {row['AttractionId']}")

        conn.commit()
        print("All updates completed successfully")

    except Exception as e:
        conn.rollback()
        print(f"Error occurred: {str(e)}")

    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    update_attractions()